<?php

include_once('data.php');
include_once(Utils::getPath(array('model', 'challenge.php')));
include_once(Utils::getPath(array('model', 'challengeCategory.php')));

class ChallengeRepository {

    private $lastFilter;
    
    public function getById($challengeId) {
        if (is_numeric($challengeId)) {
            $row = Database::select('*', 'challenges', array(array('Id = ?', $challengeId, PDO::PARAM_INT)));

            if ($row->rowCount() == 1) {
                $challenge = $row->fetchObject('Challenge');
                return $challenge;
            }
        }
        return null;
    }

    public function getAll($order = null, $desc = null, $pageIndex = 0, $pageCount = 10) {
        return $this->find(null, $order, $desc, $pageIndex, $pageCount);
    }

    public function findByCategory($categoryId, $order = null, $desc = null, $pageIndex = 0, $pageCount = 10) {
        $filterParams = array();
        $filterParams[] = array("IdCategory = ?", $categoryId, PDO::PARAM_INT);

        return $this->find($filterParams, $order, $desc, $pageIndex, $pageCount);
    }

    public function findByAuthor($authorId, $order = null, $desc = null, $pageIndex = 0, $pageCount = 10) {
        $filterParams = array();
        $filterParams[] = array("IdAuthor = ?", $authorId, PDO::PARAM_INT);

        return $this->find($filterParams, $order, $desc, $pageIndex, $pageCount);
    }

    public function findByTitle($title, $order = null, $desc = null, $pageIndex = 0, $pageCount = 10) {
        $filterParams = array();
        $filterParams[] = array('Title = ?', $title, PDO::PARAM_STR);

        return $this->find($filterParams, $order, $desc, $pageIndex, $pageCount);
    }

    public function find($filterParams, $order = null, $desc = null, $pageIndex = 0, $pageCount = 10) {
        $rows = Database::select('*', 'challenges', $filterParams, $order, $desc, $pageIndex * $pageCount, $pageCount);
        $this->lastFilter = $filterParams;
        if ($rows->rowCount() > 0) {
            $challenges = $rows->fetchAll(PDO::FETCH_CLASS, "Challenge");
            return $challenges;
        }
        return null;
    }

    public function countLast() {
        $rows = Database::select('Count(*)', 'challenges', $this->lastFilter, null, null, null, null);

        if ($rows->rowCount() == 1) {
            $row = $rows->fetch(PDO::FETCH_NUM);
            return $row[0];
        }
        return null;
    }

    private function getLast() {
        $row = Database::select('*', 'challenges', null, 'Id', true, 0, 1);

        if ($row->rowCount() == 1) {
            $challenge = $row->fetchObject('Challenge');
            return $challenge;
        }
        return null;
    }

    public function persist($challenge) {
        $values = array();

        $values[] = array('Id', $challenge->getId(), PDO::PARAM_INT);
        $values[] = array('IdCategory', $challenge->getIdCategory(), PDO::PARAM_INT);
        $values[] = array('IdAuthor', $challenge->getIdAuthor(), PDO::PARAM_INT);
        $values[] = array('Title', $challenge->getTitle(), PDO::PARAM_STR);
        $values[] = array('Description', $challenge->getDescription(), PDO::PARAM_STR);
        $values[] = array('Date', $challenge->getDate(), PDO::PARAM_STR);
        $values[] = array('SolutionKey', $challenge->getSolutionKey(), PDO::PARAM_STR);
        $values[] = array('FilePath', $challenge->getFilePath(), PDO::PARAM_STR);

        if (!$challenge->IsPersited()) {
            $res = Database::insert('challenges', $values);
            if ($res)
                return $this->getLast();
        }
        else {
            $res = DataBase::update('challenges', $values, array(array('Id = ?', $challenge->getId(), PDO::PARAM_INT)));
            if ($res)
                return $this->getById($challenge->getId());
        }

        return null;
    }

    public function remove($challenge) {
        $res = Database::remove('challenges', array(array('Id = ?', $challenge->getId(), PDO::PARAM_INT)));
        return $res;
    }

    public function getCategoryById($categoryId) {
        if (is_numeric($categoryId)) {
            $row = Database::select('*', 'challenge_categories', array(array('Id = ?', $categoryId, PDO::PARAM_INT)));

            if ($row->rowCount() == 1) {
                $challengeCategories = $row->fetchObject('ChallengeCategory');
                return $challengeCategories;
            }
        }
        return null;
    }

    public function getAllCategories($order = null, $desc = null, $pageIndex = 0, $pageCount = 10) {
        return $this->findCategory(null, $order, $desc, $pageIndex, $pageCount);
    }

    public function findCategory($filterParams, $order = null, $desc = null, $pageIndex = 0, $pageCount = 10) {
        $rows = Database::select('*', 'challenge_categories', $filterParams, $order, $desc, $pageIndex * $pageCount, $pageCount);

        if ($rows->rowCount() > 0) {
            $challengeCategories = $rows->fetchAll(PDO::FETCH_CLASS, "ChallengeCategory");
            return $challengeCategories;
        }
        return null;
    }
    
    public function getChallengeListForUser($id)
    {
        $query = "SELECT
            challenges.id AS id,
            challenges.idcategory AS category,
            users.username AS author,
            users.id AS authorid,
            challenges.title,
            challenge_solutions.num_solutions AS solutions,
            user_solutions.num_user_solutions AS usersolutions

        FROM challenges
            JOIN 
                users 
            ON challenges.idauthor = users.id
            LEFT JOIN 
                (SELECT idchallenge, COUNT( DISTINCT idauthor ) AS num_solutions FROM solutions WHERE solutions.valid = true GROUP BY idchallenge) AS challenge_solutions
            ON challenges.Id = challenge_solutions.IdChallenge
            LEFT JOIN
                (SELECT idchallenge, COUNT( id ) AS num_user_solutions FROM solutions WHERE solutions.valid = true AND idauthor = :id GROUP BY idchallenge) AS user_solutions
            ON challenges.id = user_solutions.idchallenge

        ORDER BY category, id";

        $arguments = array();
        $arguments[] = array(":id", $id, PDO::PARAM_INT);

        $challenges = Database::customQuery($query, $arguments)->fetchAll();
        
        return $challenges;
    }
    
    public function getUsersWhoSolvedChallenge($id)
    {
        $query = "SELECT DISTINCT idauthor FROM solutions WHERE valid = true AND idchallenge = :id";

        $arguments = array();
        $arguments[] = array(":id", $id, PDO::PARAM_INT);

        $users = Database::customQuery($query, $arguments)->fetchAll();
        
        return $users;
    }

}

?>